const mysql = require('mysql2');

class MysqlConnection { 
    constructor(host, port, user, password, database) {
        this.connection = null;
        this.state = 0; // 0.未连接 1.正在连接 2.已连接 3.正在重连 4. 连接异常(也重连)
        this.host = host
        this.port = port
        this.user = user
        this.password = password
        this.database = database
    }

    handleConnect() {
        this.connection = mysql.createConnection({
            host: this.host,
            user: this.user,
            password: this.password,
            database: this.database,
            port: this.port,
        });

        this.connection.connect((err) => {
            if (!err) {
                this.state = 2
                console.log(new Date(), '✅ 成功连接到数据库 - threadId:', this.connection.threadId);
            } 
        });

        // 监听错误事件
        this.connection.on('error', (err) => {
            this.state = 3
            if (err.code === 'ECONNREFUSED') {
                console.log(new Date(), '连接失败，正在尝试重新连接...');
            } else if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                console.log(new Date(), '连接丢失，正在尝试重新连接...');
            } else if(err.code === 'ECONNRESET') {
                console.log(new Date(), '连接被重置，正在尝试重新连接...');
            }else {
            //   throw err;
                this.state = 4
                console.log(new Date(), "未知错误",err.code)
            }
            setTimeout(() => {
                this.handleConnect()
            }, 3000);
        })

        // 监听连接关闭事件
        this.connection.on('close', () => {
            console.warn(new Date(),'⚠️ 数据库连接已关闭，尝试重连...');
            setTimeout(() => {
                this.handleConnect()
            }, 3000);
        });


    }

    SelectTest() {
        if(this.connection && this.state === 2) {
            this.connection.query('SELECT * FROM `user` LIMIT 10', (err, results) => {
                if (err) {
                    console.log("query error",err.message);
                    return
                }
                console.log("query success: ",results.length, new Date());
            })
        }
    }

    SaveTest() {
        let data = "[{\"spec_type\":\"uint16\",\"addr\":\"55-00-02-002\",\"alert\":\"\",\"alert_type\":null,\"field_name\":\"温度℃\",\"value\":27.3,\"create_time\":\"2025-06-20 14:06:55\"},{\"spec_type\":\"uint16\",\"addr\":\"55-00-02-003\",\"alert\":\"\",\"alert_type\":null,\"field_name\":\"湿度%\",\"value\":42.1,\"create_time\":\"2025-06-20 14:06:55\"}]"
        try {
            let record_list = JSON.parse(data)
            // console.log("record_list: ",record_list)
            if(record_list.length > 0 && this.connection && this.state === 2) {
                let sql_str = "INSERT INTO var_record2 (addr, field_name, spec_type, value, create_time) VALUES ";
                let arr = []
                for(let record of record_list) {
                    arr.push(`('${record.addr}', '${record.field_name}', '${record.spec_type}', ${record.value}, '${record.create_time}')`)
                }
                let sql_str_part2 = arr.join(",")
                sql_str += sql_str_part2
                this.connection.query(sql_str, (err, results) => {
                    if (err) {
                        console.log(new Date(),"INSERT error",err.message);
                        return
                    }
                    console.log(new Date(),"INSERT success: ",results.insertId);
                })
            }
        } catch (error) {
            console.log(new Date(),"parse error",error)
        }
        // let record_data = JSON.parse(data)
    }

}


const mysqlCon = new MysqlConnection()

mysqlCon.host= "192.168.1.51"
mysqlCon.port= 3307
mysqlCon.user= "root"
mysqlCon.password= "root"
mysqlCon.database= "configuration"
mysqlCon.handleConnect()
setInterval(() => {
    // console.log("mysql state: ",mysqlCon.state)
    mysqlCon.SaveTest()
}, 1500)

/*
const insertQuery = "INSERT INTO users (name, age) VALUES (?, ?)";
  const values = ['张三', 25];

  connection.query(insertQuery, values, (error, results, fields) => {
    if (error) throw error;

    console.log('插入的记录ID:', results.insertId); // 新插入行的行 ID
  });
*/
